const sqlite3 = require('sqlite3').verbose();

class SQLiteUtils {
  constructor(dbFileName) {
    this.db = new sqlite3.Database(dbFileName, (err) => {
      if (err) {
        console.error('Error opening database:', err.message);
      } else {
        console.log('Connected to the database.');
      }
    });
  }

  // 执行查询
  query(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          reject('Error querying data:', err.message);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // 执行插入
  insert(sql, params = []) {
    return new Promise((resolve, reject) => {
      const stmt = this.db.prepare(sql);
      stmt.run(params, function (err) {
        if (err) {
          reject('Error inserting data:', err.message);
        } else {
          resolve(this.lastID);
        }
      });
      stmt.finalize();
    });
  }

  // 执行更新
  update(sql, params = []) {
    return new Promise((resolve, reject) => {
      const stmt = this.db.prepare(sql);
      stmt.run(params, function (err) {
        if (err) {
          reject('Error updating data:', err.message);
        } else {
          resolve(this.changes);
        }
      });
      stmt.finalize();
    });
  }

  // 执行删除
  delete(sql, params = []) {
    return new Promise((resolve, reject) => {
      const stmt = this.db.prepare(sql);
      stmt.run(params, function (err) {
        if (err) {
          reject('Error deleting data:', err.message);
        } else {
          resolve(this.changes);
        }
      });
      stmt.finalize();
    });
  }

  // 执行事务操作
  runTransaction(operations) {
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        this.db.run('BEGIN TRANSACTION');
        Promise.all(operations.map(op => op()))
          .then(() => {
            this.db.run('COMMIT', (err) => {
              if (err) {
                reject('Error committing transaction:', err.message);
              } else {
                resolve('Transaction successful');
              }
            });
          })
          .catch((err) => {
            this.db.run('ROLLBACK', () => {
              reject('Transaction rolled back due to error:', err);
            });
          });
      });
    });
  }

  // 关闭数据库连接
  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) {
          reject('Error closing database:', err.message);
        } else {
          resolve('Database connection closed.');
        }
      });
    });
  }
}

module.exports = SQLiteUtils;
